/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import DTO.Member;
import DTO.Members;
import DTO.Minestones;
import DTO.Projects;
import DTO.Users;
import DTO.Minestone;
import DTO.Project;
import DTO.Task;
import DTO.Tasks;
import DTO.User;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import util.DBUtil;

/**
 *
 * @author ThanhTai
 */
public class ProjectDAO {

    public Projects GetAllProject(String condition) throws SQLException, Exception {

        DBUtil util = new DBUtil();
        Connection conn = null;
        Statement stm = null;
        StringBuilder strBuilder = new StringBuilder();
        List<Project> projectList = new ArrayList<Project>();
        ResultSet rs = null;
        Project project;

        try {
            Class.forName(util.getDriverName());
            DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
            conn = DriverManager.getConnection(util.getURL(), util.getUsername(), util.getPassword());
            strBuilder.append("SELECT * FROM Projects");
            if (condition != null && !condition.equals("")) {
                strBuilder.append(condition);
            }
            stm = conn.createStatement();
            rs = stm.executeQuery(strBuilder.toString());
            projectList = new ArrayList<Project>();
            while (rs.next()) {
                project = new Project(rs.getInt("ProjectID"), rs.getString("ProjectManager"),
                        rs.getString("ProjectName"), rs.getString("Description"), rs.getInt("NumOfMinestone"),
                        new DTO.Date(rs.getDate("StartDay")), new DTO.Date(rs.getDate("EndDay")), rs.getString("Status"));
                projectList.add(project);
            }
            return new Projects(projectList);
        } catch (SQLException e) {
            throw (e);
        } catch (Exception e) {
            throw (e);
        } finally {
            try {
                rs.close();
            } catch (SQLException e) {
                throw (e);
            }
            try {
                stm.close();
            } catch (SQLException e) {
                throw (e);
            }
            try {
                conn.close();
            } catch (SQLException e) {
                throw (e);
            }
        }
    }

    public static Project InsertProject(Project project) throws SQLException, Exception {

        DBUtil util = new DBUtil();
        Connection conn = null;
        try {
            Class.forName(util.getDriverName());
            DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
            conn = DriverManager.getConnection(util.getURL(), util.getUsername(), util.getPassword());
            PreparedStatement stm = null;
            if (conn != null) {
                int returnID = -1;

                String sql = "Insert into Projects(ProjectName, ProjectManager, Description, NumOfMinestone, StartDay, EndDay, Status) value (?, ?, ?, ?, ?, ?, ?)";
                stm = conn.prepareStatement(sql);
                stm.setString(1, project.getProjectManager());
                stm.setString(2, project.getProjectName());
                stm.setString(3, project.getDescription());
                stm.setInt(4, project.getNumOfMinestone());
                stm.setDate(5, project.getStartDay().getDateJavaUtil());
                stm.setDate(6, project.getEndDay().getDateJavaUtil());
                stm.setString(7, project.getStatus());

                stm.executeUpdate();

                ResultSet rs = stm.getGeneratedKeys();
                if (rs.next()) {
                    returnID = rs.getInt(1);
                }

                if (returnID != -1) {
                    project.setProjectID(returnID);
                }
                rs.close();
                stm.close();
                conn.close();
                return project;
            }
            return null;
        } catch (SQLException ex) {
            ex.printStackTrace();
            return null;
        }
    }

    public static Projects InsertProjects(Projects projects) throws SQLException, Exception {
        try {
            Projects result = new Projects();
            for (int i = 0; i < projects.size(); i++) {
                Project project = InsertProject(projects.get(i));
                if (project.getProjectID() != -1) {
                    result.add(project);
                }
            }
            return result;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public Users GetAllMemberOfProject(int projectID) throws SQLException, Exception {

        DBUtil util = new DBUtil();
        Users users = new Users();
        String query = "";
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstm = null;
        try {
            Class.forName(util.getDriverName());
            DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
            conn = DriverManager.getConnection(util.getURL(), util.getUsername(), util.getPassword());
            query = "SELECT u.* FROM Users u LEFT JOIN Members m ON u.username = m.username WHERE m.ProjectID = ?";
            pstm = conn.prepareStatement(query);
            pstm.setInt(1, projectID);
            rs = pstm.executeQuery();

            while (rs.next()) {
                String username = rs.getString(1);
                String password = rs.getString(2);
                String fullname = rs.getString(3);
                String userRole = rs.getString(4);
                User user = new User(username, password, fullname, userRole);
                users.add(user);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                rs.close();
                pstm.close();
                conn.close();
            } catch (SQLException sqlEx) {
                sqlEx.printStackTrace();
            }
        }
        return users;
    }

    public Members GetAllMembersOfProject(int projectID) throws SQLException, Exception {

        DBUtil util = new DBUtil();
        Members members = new Members();
        String query = "";
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstm = null;
        try {
            Class.forName(util.getDriverName());
            DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
            conn = DriverManager.getConnection(util.getURL(), util.getUsername(), util.getPassword());
            query = "SELECT * FROM Members WHERE ProjectID = ?";
            pstm = conn.prepareStatement(query);
            pstm.setInt(1, projectID);
            rs = pstm.executeQuery();

            while (rs.next()) {
                Integer memberID = rs.getInt(1);
                Integer projectID1 = rs.getInt(2);
                String username = rs.getString(3);
                String status = rs.getString(4);
                Member member = new Member(memberID, projectID1, username, status);
                members.add(member);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                rs.close();
                pstm.close();
                conn.close();
            } catch (SQLException sqlEx) {
                sqlEx.printStackTrace();
            }
        }
        return members;
    }

    public Minestones GetAllMinestone(int projectID) throws SQLException, Exception {

        DBUtil util = new DBUtil();
        Minestones list = new Minestones();
        String query = "";
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstm = null;
        try {
            Class.forName(util.getDriverName());
            DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
            conn = DriverManager.getConnection(util.getURL(), util.getUsername(), util.getPassword());
            query = "SELECT * FROM Minestones";
            if (projectID > 0) {
                query = query + " WHERE ProjectID = ?";
                pstm = conn.prepareStatement(query);
                pstm.setInt(1, projectID);
            } else {
                pstm = conn.prepareStatement(query);
            }
            rs = pstm.executeQuery();

            while (rs.next()) {
                int minestoneID = rs.getInt(1);
                String minestoneName = rs.getString(2);
                int ProjectID = rs.getInt(3);
                String description = rs.getString(4);
                DTO.Date startDay = new DTO.Date(rs.getDate(5));
                DTO.Date endDate = new DTO.Date(rs.getDate(6));
                String Status = rs.getString(7);
                Minestone minestone = new Minestone(minestoneID, minestoneName, ProjectID, description,
                        startDay, endDate, Status);
                list.add(minestone);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                rs.close();
                pstm.close();
                conn.close();
            } catch (SQLException sqlEx) {
                sqlEx.printStackTrace();
            }
        }
        return list;

    }

    public Tasks GetAllTasksbyMinestoneID(int minestoneID) throws SQLException, Exception {

        DBUtil util = new DBUtil();
        Tasks list = new Tasks();
        String query = "";
        Connection conn = null;
        ResultSet rs = null;
        PreparedStatement pstm = null;
        try {
            Class.forName(util.getDriverName());
            DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
            conn = DriverManager.getConnection(util.getURL(), util.getUsername(), util.getPassword());
            query = "SELECT * FROM Tasks";
            if (minestoneID > 0) {
                query = query + " WHERE MinestoneID = ?";
                pstm = conn.prepareStatement(query);
                pstm.setInt(1, minestoneID);
            } else {
                pstm = conn.prepareStatement(query);
            }
            rs = pstm.executeQuery();

            while (rs.next()) {
                int taskID = rs.getInt(1);
                String taskName = rs.getString(2);
                String username = rs.getString(4);
                String description = rs.getString(5);
                DTO.Date startDay = new DTO.Date(rs.getDate(6));
                DTO.Date endDate = new DTO.Date(rs.getDate(7));
                String Status = rs.getString(8);
                Task task = new Task(taskID, taskName, minestoneID, username, description,
                        startDay, endDate, Status);
                list.add(task);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                rs.close();
                pstm.close();
                conn.close();
            } catch (SQLException sqlEx) {
                sqlEx.printStackTrace();
            }
        }
        return list;

    }

    public Minestones GetAllMinestoneOfUsername(String username) throws SQLException, Exception {

        DBUtil util = new DBUtil();
        Connection conn = null;
        Statement stm = null;
        StringBuilder strBuilder = new StringBuilder();
        List<Minestone> minestoneList = new ArrayList<Minestone>();
        ResultSet rs = null;
        Minestone minestone;

        try {
            Class.forName(util.getDriverName());
            DriverManager.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());
            conn = DriverManager.getConnection(util.getURL(), util.getUsername(), util.getPassword());
            strBuilder.append("SELECT distinct mi.* FROM Minestones mi right join "
                    + "(SELECT distinct p.* FROM Projects p  left join Members m on p.ProjectID = m.ProjectID "
                    + "WHERE m.username = '" + username
                    + "') ex on ex.ProjectID = mi.ProjectID");
            stm = conn.createStatement();
            rs = stm.executeQuery(strBuilder.toString());
            minestoneList = new ArrayList<Minestone>();
            while (rs.next()) {
                minestone = new Minestone(rs.getInt(1), rs.getString(2), rs.getInt(3), rs.getString("Description"),
                        new DTO.Date(rs.getDate("StartDay")), new DTO.Date(rs.getDate("EndDay")), rs.getString("Status"));
                minestoneList.add(minestone);
            }
            return new Minestones(minestoneList);
        } catch (SQLException e) {
            throw (e);
        } catch (Exception e) {
            throw (e);
        } finally {
            try {
                rs.close();
            } catch (SQLException e) {
                throw (e);
            }
            try {
                stm.close();
            } catch (SQLException e) {
                throw (e);
            }
            try {
                conn.close();
            } catch (SQLException e) {
                throw (e);
            }
        }
    }
}
